package net.funtip.jdbc.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Collection;

import javax.sql.DataSource;

import net.funtip.jdbc.JdbcLoggerException;
import net.funtip.jdbc.entity.QueryStatistic;
import net.funtip.jdbc.entity.QueryStatisticImpl;

public class QueryStatisticDaoJdbcImpl implements QueryStatisticDao {
	
	private static final String SQL_SELECT = "SELECT QS_ID, QS_QUERY, QS_PERCENTAGE100, QS_EXECUTION_COUNT," +
			" QS_EXECUTION_TIME, QS_NEXT_COUNT, QS_NEXT_TIME FROM QUERY_STATISTICS ";
	
	private static final String QUERY_DELETE_ALL = "DELETE FROM QUERY_STATISTICS";
	private static final String QUERY_DELETE_BY_NAME = "DELETE FROM QUERY_STATISTICS WHERE QS_QUERY = ?";
	private static final String QUERY_ALL = SQL_SELECT ;
	private static final String QUERY_BY_NAME = SQL_SELECT + " WHERE QS_QUERY = ?";
	private static final String QUERY_INSERT = "INSERT INTO QUERY_STATISTICS (QS_QUERY, QS_PERCENTAGE100, " +
			"QS_EXECUTION_COUNT, QS_EXECUTION_TIME, QS_NEXT_COUNT, QS_NEXT_TIME, QS_ID) " +
			"VALUES (?,?,?,?,?,?,NEXTVAL FOR SEQ_FTID)";
	private static final String QUERY_UPDATE = "UPDATE QUERY_STATISTICS SET QS_QUERY = ?, QS_PERCENTAGE100 = ?, " +
			"QS_EXECUTION_COUNT = ?, QS_EXECUTION_TIME = ?, QS_NEXT_COUNT = ?, QS_NEXT_TIME = ? " +
			"WHERE QS_ID = ?";
	
	protected DataSource dataSource;
	protected boolean singleUserMode;
	protected Long lastNewId;
	
	public QueryStatisticDaoJdbcImpl() {
		this.singleUserMode = true;
	}
	
	public void setDataSource(DataSource dataSource) {
		this.dataSource = dataSource;
	}

	public QueryStatistic create(String name) {
		return new QueryStatisticImpl(name);
	}

	public void close(PreparedStatement pstmt) {
		if (pstmt == null)
			return;
		try {
			pstmt.close();
		} catch (SQLException e) {
		}
	}
	
	public void close(Statement stmt) {
		if (stmt == null)
			return;
		try {
			stmt.close();
		} catch (SQLException e) {
		}
	}
	
	public void close(ResultSet rset) {
		if (rset == null)
			return;
		try {
			rset.close();
		} catch (SQLException e) {
		}
	}
	
	public void close(Connection conn) {
		if (conn == null)
			return;
		try {
			conn.close();
		} catch (SQLException e) {
		}
	}
	
	public void deleteAll() {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(QUERY_DELETE_ALL);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new JdbcLoggerException(e.getMessage(),e);
		} finally {
			close(pstmt);
			close(conn);
		}
	}

	public void deleteByName(String name) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(QUERY_DELETE_BY_NAME);
			pstmt.setString(1,name);
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new JdbcLoggerException(e.getMessage(),e);
		} finally {
			close(pstmt);
			close(conn);
		}
	}

	public Collection<QueryStatistic> get() {
		Collection<QueryStatistic> l = new ArrayList<QueryStatistic>();
		Connection conn = null;
		PreparedStatement pstmt = null;
		try {
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(QUERY_ALL);
			ResultSet rset = pstmt.executeQuery();
			while (rset.next()) {
				l.add(createByResultSet(rset));
			}
			return l;
		} catch (SQLException e) {
			throw new JdbcLoggerException(e.getMessage(),e);
		} finally {
			close(pstmt);
			close(conn);
		}
	}

	public QueryStatistic getByName(String name) {
		QueryStatistic r = null;
		Connection conn = null;
		PreparedStatement pstmt = null;
		ResultSet rset = null;
		try {
			conn = dataSource.getConnection();
			pstmt = conn.prepareStatement(QUERY_BY_NAME);
			pstmt.setString(1,name);
			rset = pstmt.executeQuery();
			if (rset.next()) {
				r = createByResultSet(rset);
			}
			return r;
		} catch (SQLException e) {
			throw new JdbcLoggerException(e.getMessage(),e);
		} finally {
			close(rset);
			close(pstmt);
			close(conn);
		}
	}

	public void store(QueryStatistic qs) {
		Connection conn = null;
		PreparedStatement pstmt = null;
		
		try {
			conn = dataSource.getConnection();
			if (qs.getId() == null) {
				pstmt = conn.prepareStatement(QUERY_INSERT);
			} else {
				pstmt = conn.prepareStatement(QUERY_UPDATE);
			}

			int i = 1;
			String sqlStatementString = qs.getName();
			if (sqlStatementString.length() > 1500) {
				sqlStatementString = sqlStatementString.substring(0, 1500);
			}
			pstmt.setString(i++, sqlStatementString);
			pstmt.setInt(i++, qs.getPercentage());
			pstmt.setInt(i++, qs.getExecutionCount());
			pstmt.setLong(i++, qs.getExecutionTime());
			pstmt.setLong(i++, qs.getNextCount());
			pstmt.setLong(i++, qs.getNextTime());
			if (qs.getId() != null) {
				// only for update statements
				pstmt.setLong(i++, qs.getId());
			}
			pstmt.executeUpdate();
		} catch (SQLException e) {
			throw new JdbcLoggerException(e.getMessage() + ": " + qs, e);
		} finally {
			close(pstmt);
			close(conn);
		}
	}

	protected QueryStatistic createByResultSet(ResultSet rset) throws SQLException {
		QueryStatistic qs = create(null);
		int i = 1;
		qs.setId(rset.getLong(i++));
		qs.setName(rset.getString(i++));
		qs.setPercentage(rset.getInt(i++));
		qs.setExecutionCount(rset.getInt(i++));
		qs.setExecutionTime(rset.getLong(i++));
		qs.setNextCount(rset.getLong(i++));
		qs.setNextTime(rset.getLong(i++));
		return qs;
	}

}
